Create       proc sp_create_update_legacy_diebank_lot (

	@PART_NUMBER VARCHAR(50), 
	@LOT_NUMBER VARCHAR(50), 
	@QTY_IN INT,
	@QTY_OUT INT,
	@DATE_IN DATETIME,
	@DATE_OUT DATETIME,
	@INVENTORY_TYPE VARCHAR(50),
	@VENDOR_NAME VARCHAR(50),
	@VENDOR_LOTNUMBER VARCHAR(500),
	@WAFER_ID VARCHAR(100)
)

AS
BEGIN

	DECLARE @PARENT_PRD_ID INT
	DECLARE @BASE_PART_NUMBER VARCHAR(50)
	DECLARE @PROCESS_STEP_ID INT
	DECLARE @LOCATION_ID INT
	DECLARE @INT INT
	DECLARE @NORMAL_COST FLOAT
	DECLARE @LOT_ID INT
	
	DECLARE @DATE_TIME DATETIME
	DECLARE @START_DATE DATETIME
	DECLARE @END_DATE DATETIME

	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT, PROCESS_STEP WHERE PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT.PART_NUMBER LIKE @PART_NUMBER AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @INVENTORY_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
	
	IF @LOCATION_ID = '' OR @LOCATION_ID IS NULL
	BEGIN
		-- dummy update to avoid SQLException
		UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
		RETURN
	END
		
	IF @PROCESS_STEP_ID IS NULL -- new Product or Product Name mismatch
	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER LIKE @PART_NUMBER AND PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID AND PRODUCT_MAP_TABLE.LOCATION_ID = @LOCATION_ID
		IF @PROCESS_STEP_ID IS NULL
		BEGIN
			EXEC SP_CREATE_UPDATE_MISCELLANEOUS_LOT @PART_NUMBER, @LOT_NUMBER,@VENDOR_LOTNUMBER, NULL, NULL, NULL,
			@VENDOR_NAME, @INVENTORY_TYPE, @INVENTORY_TYPE, NULL, @DATE_IN, @DATE_OUT, NULL, NULL, NULL, NULL, @QTY_IN, @QTY_OUT, NULL, NULL, 'RUNNING'

			-- DUMMY UPDATE TO AVOID SQLEXCEPTION
			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
			RETURN
		END
	END

	IF @PROCESS_STEP_ID IS NOT NULL
	BEGIN
		UPDATE MISCELLANEOUS_LOT_TABLE SET STATUS = 'CLOSE' WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PS_TYPE_NAME LIKE @INVENTORY_TYPE
	END

	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER

	PRINT 'LOT_ID = '+CONVERT(VARCHAR, @LOT_ID)
	
	EXEC SP_CLOSE_TRANSIT_LOTS_BY_SHIPPNG_LOT_ID @PROCESS_STEP_ID, @LOT_NUMBER, @LOCATION_ID, @QTY_IN, @DATE_IN

	-- Create Lot if it doesnt exist
	IF @LOT_ID IS NULL
	BEGIN 
		SELECT @NORMAL_COST = PSLT_COST_NORMAL FROM PROCESS_STEP_LOCATION_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, LOT_COST, LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY) VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, (@NORMAL_COST * @QTY_IN), 'RUNNING', CURRENT_TIMESTAMP, 0 )
		SELECT @LOT_ID = @@IDENTITY
	END
	
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'RUNNING', LOT_ENTERED_BY = 0 WHERE LOT_ID = @LOT_ID
	END	


	-- insert/update Inventory Lot
	DECLARE @INV_LOT_ID INT

	
	DECLARE @TEMP_VARCHAR VARCHAR(20)
	SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 12:00AM'
	SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	SET @TEMP_VARCHAR = NULL
	SET @TEMP_VARCHAR = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)+' 11:59PM' 
	SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

	SELECT @INV_LOT_ID = LOT_ID FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID

	IF @INV_LOT_ID IS NULL
	BEGIN

		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_QTY_OUT, IL_DATE_IN, IL_DATE_OUT, IL_WAFER_QTY_IN, TIMESTAMP,IL_VENDOR_LOT_NUMBER, WAFER_ID) VALUES (@LOT_ID, @QTY_IN, @QTY_OUT, @DATE_IN, @DATE_OUT, 0, CURRENT_TIMESTAMP, @VENDOR_LOTNUMBER, @WAFER_ID)
	END	
	ELSE
	BEGIN		
		UPDATE INVENTORY_LOT SET IL_DATE_IN = @DATE_IN, IL_QTY_IN = @QTY_IN, IL_WAFER_QTY_IN = 0, TIMESTAMP = CURRENT_TIMESTAMP, WAFER_ID = @WAFER_ID WHERE LOT_ID = @INV_LOT_ID
		
	END
	
END
